package com.gisterrier.server.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.common.db.DBConnection;
import com.common.shared.ClSelectionItem;
import com.gisterrier.shared.BuildingCustomerShort;
import com.gisterrier.shared.Customer;
import com.gisterrier.shared.GIsMap_SocarNet;
import com.gisterrier.shared.Map_Customer_to_Building;
import com.gisterrier.shared.Map_Customer_to_Network;
import com.gisterrier.shared.Map_Street_to_Street;
import com.gisterrier.shared.ObjSelectionresult;

public class MDBConnection {

	private final static String CUSTOMER_FIeldS = "c.cusid,c.cusname,c.streetid,c.phone,c.custypeid,c.job,c.hometypeid,c.startamount,c.startdate,c.ndoc,c.home,c.flat,c.userr,c.cusstatusid, c.classid, c.scope_id, c.gasspressid,c.business_id,c.private_number, c.cus_inn";

	public static Customer getSelectionItems(int customerId) throws Exception {
		Customer result = null;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select "
				+ CUSTOMER_FIeldS
				+ ", mc.building_id "
				+ " from customer c left join map_customer_to_building mc on c.cusid=cus_id"
				+ " where c.cusid=" + customerId;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result = DBMapping.getCustomer(rs);
				result.setBuilding_id(rs.getLong("building_id"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Customer> getCustomersForBuilding(long building_id)
			throws Exception {
		ArrayList<Customer> result = new ArrayList<Customer>();

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select "
				+ CUSTOMER_FIeldS
				+ " "
				+ "from customer c inner join map_customer_to_building cb on cb.cus_id=c.c.cusid "
				+ "where cb.building_id=" + building_id;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBMapping.getCustomer(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<BuildingCustomerShort> getBuildingCustomerShorts(
			long street_id, Long zone) throws Exception {
		ArrayList<BuildingCustomerShort> result = new ArrayList<BuildingCustomerShort>();

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select c.cusid,cusname,case when building_id is null then -1 else building_id end building_id,streetid"
				+ " from customer c left join map_customer_to_building mb on mb.cus_id=c.cusid "
				+ "where streetid="
				+ street_id
				+ (zone != null && zone.longValue() > 0 ? " and zone="
						+ zone.longValue() : "");
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBMapping.getBuildingCustomerShort(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<ClSelectionItem> getZones(Long regionId,
			Long subRegionId, Long cityId, Long streetid) throws Exception {
		ArrayList<ClSelectionItem> result = new ArrayList<ClSelectionItem>();
		String sql = "select distinct zone from v_customer_full where 1=1";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String where = "";
			if (regionId != null && regionId.longValue() > 0)
				where += " and regionid=" + regionId.longValue();
			if (subRegionId != null && subRegionId.longValue() > 0)
				where += " and subregionid=" + subRegionId.longValue();
			if (cityId != null && cityId.longValue() > 0)
				where += " and cityid=" + cityId.longValue();
			if (cityId != null && cityId.longValue() > 0)
				where += " and streetid=" + cityId.longValue();
			sql += where;
			con = DBConnection.getConnection("Gass");
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();
			while (rs.next()) {
				long zone = rs.getLong(1);
				ClSelectionItem value = new ClSelectionItem();
				value.setId(zone);
				value.setValue(zone + "");
				result.add(value);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<BuildingCustomerShort> getBuildingCustomerShortsForBuilding(
			long building_id) throws Exception {
		ArrayList<BuildingCustomerShort> result = new ArrayList<BuildingCustomerShort>();

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select c.cusid,cusname,case when building_id is null then -1 else building_id end building_id,streetid"
				+ " from customer c inner join map_customer_to_building mb on mb.cus_id=c.cusid "
				+ "where building_id=" + building_id;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(DBMapping.getBuildingCustomerShort(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static Long insertMap_Street_to_Street(Map_Street_to_Street street)
			throws Exception {
		Long result = null;

		Connection con = null;
		CallableStatement stmt = null;

		String sql = "{ ? = CALL insert_map_street_to_street(?,?) }";

		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.prepareCall(sql);

			stmt.registerOutParameter(1, java.sql.Types.BIGINT);
			stmt.setLong(2, street.getMap_street_id());
			stmt.setLong(3, street.getBilling_street_id());
			stmt.executeUpdate();
			result = stmt.getLong(1);
			con.commit();

		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static Long insertMap_Customer_to_Building(
			Map_Customer_to_Building mapbuildCust) throws Exception {
		Long result = null;

		Connection con = null;
		CallableStatement stmt = null;

		String sql = "{ ? = CALL insert_map_customer_to_building(?,?) }";

		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.prepareCall(sql);

			stmt.registerOutParameter(1, java.sql.Types.BIGINT);
			stmt.setLong(2, mapbuildCust.getCus_id());
			stmt.setLong(3, mapbuildCust.getBuilding_id());
			stmt.executeUpdate();
			result = stmt.getLong(1);
			con.commit();

		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Long> insertMap_Customer_to_Building(
			ArrayList<BuildingCustomerShort> mapbuildCusts) throws Exception {
		ArrayList<Long> result = new ArrayList<Long>();

		Connection con = null;
		CallableStatement stmt = null;

		String sql = "{ ? = CALL insert_map_customer_to_building(?,?) }";

		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.prepareCall(sql);

			stmt.registerOutParameter(1, java.sql.Types.BIGINT);
			for (BuildingCustomerShort buildingCustomerShort : mapbuildCusts) {
				stmt.setLong(2, buildingCustomerShort.getCusid());
				stmt.setLong(3, buildingCustomerShort.getBuilding_id());
				stmt.executeUpdate();
				result.add(stmt.getLong(1));
			}

			con.commit();

		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static Long insertMap_Map_Customer_to_Network(
			Map_Customer_to_Network mapsocarCust) throws Exception {
		Long result = null;

		Connection con = null;
		CallableStatement stmt = null;

		String sql = "{ ? = CALL insert_map_customer_to_network(?,?) }";

		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.prepareCall(sql);

			stmt.registerOutParameter(1, java.sql.Types.BIGINT);
			stmt.setLong(2, mapsocarCust.getCus_id());
			stmt.setLong(3, mapsocarCust.getTube_id());
			stmt.executeUpdate();
			result = stmt.getLong(1);
			con.commit();

		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			throw new Exception(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static Map_Street_to_Street getMap_Street_to_Street(
			long mapstreet_or_bill_street_id, boolean mapstreet)
			throws Exception {
		Map_Street_to_Street result = null;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select id,map_street_id,billing_street_id,streetname,cityid,subregionid,region from map_street_to_street_v c"
				+ " where "
				+ (mapstreet ? "map_street_id" : "billing_street_id")
				+ "="
				+ mapstreet_or_bill_street_id;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = DBMapping.getMap_Street_to_Street(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ObjSelectionresult getObjSelectionresult(long building_id)
			throws Exception {
		ObjSelectionresult result = null;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select raioni, regioni,cityid,streetid from map_customer_to_building_v "
				+ " where building_id=" + building_id + " limit 1 ";
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = DBMapping.getObjSelectionresult(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Map_Customer_to_Building> getMap_Customer_to_Building(
			long mapstreet_or_bill_street_id, boolean mapbuild)
			throws Exception {
		ArrayList<Map_Customer_to_Building> result = null;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select id,cus_id,building_id, cusname from map_customer_to_building cb inner join customer c on c.cusid=cus_id "
				+ " where "
				+ (mapbuild ? "building_id" : "cus_id")
				+ "="
				+ mapstreet_or_bill_street_id;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			result = new ArrayList<Map_Customer_to_Building>();
			while (rs.next()) {
				result.add(DBMapping.getMap_Customer_to_Building(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static ArrayList<Map_Customer_to_Network> getMap_Customer_to_Network(
			long mapstreet_or_bill_street_id, boolean mapbuild)
			throws Exception {
		ArrayList<Map_Customer_to_Network> result = null;

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select id,cust_id,tube_id, cusname from map_customer_to_network cb inner join customer c on c.cusid=cust_id "
				+ " where "
				+ (mapbuild ? "tube_id" : "cust_id")
				+ "="
				+ mapstreet_or_bill_street_id;
		try {
			con = DBConnection.getConnection("Gass");
			stmt = con.createStatement();

			rs = stmt.executeQuery(sql);
			result = new ArrayList<Map_Customer_to_Network>();
			while (rs.next()) {
				result.add(DBMapping.getMap_Customer_to_Network(rs));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}

	public static GIsMap_SocarNet getGisMap_Building(long socarmap_id)
			throws Exception {
		GIsMap_SocarNet result = null;
		String sql = "select gid, street,raioni,regioni,city,objekt_id from geo_buildings where gid="
				+ socarmap_id;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection("GeoMap");
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				result = DBMapping.getGIsMap_SocarNet(rs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				stmt.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
			try {
				con.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return result;
	}
}
